package statements;

public class createTables {
	
	/* continents table- create, insert*/
	public static String dropContinentsTable="Drop table if exists continents;";
	public static String createContinentsTable=  "CREATE table continents( name varchar(100), id INT(11) NOT NULL ,PRIMARY KEY(id));";
	public static String updateContinentsTable= "INSERT into continents(name, id) Values(?,?);";
	/*update*/
	/*delete*/
	
	/* countries table -create,insert*/
	public static String dropCountriesTable="Drop table if exists countries;";
	public static String createCountriesTable= "CREATE table countries( country_name varchar(100), country_id int(11) , continent_id int(11), PRIMARY KEY(country_id),constraint Foreign key (continent_id) references continents(id) on delete restrict on update restrict));";
	public static String updateCountriesTable="INSERT into countries(country_name, country_id, continent_id) Values(?,?,?);";
	
	/*update*/
	/*delete*/
	
	/* cities table- create insert*/
	public static String dropCitiesTable="Drop table if exists cities;";
	 public static String createCitiesTable= "CREATE table cities(countryID INT(11) NOT NULL , city_name varchar(100),latitude float, longitude float ,city_id INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY(city_id),FOREIGN KEY (countryID) REFERENCES countries(country_id));";
	 public static String updateCitiesTable="INSERT into cities(countryID ,city_name, latitude, longitude ) Values(?,?,?,?);";
	public static String createIndex="ALTER TABLE `DbMysql11`.`cities` ;"+"ADD INDEX `Name` (`city_name` ASC);";
	 /*update*/
	 /*delete*/

	
	/* category table- create, insert*/
	public static String dropCategoriesTable="Drop table if exists categories;";
	public static String createCategoriesTable="CREATE table categories(categoryName varchar(45), categoryID INT(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY(categoryID));";
	public static String updateCategoriesTable="INSERT into categories(categoryName) Values (?);";
	/*update*/
	/*delete*/

	
	/*people table - create insert*/
	public static String dropPeopleTable="Drop table if exists people;";
	public static String createPeopleTable="Create table people(id INT(11) NOT NULL,name varchar(100),birth_city varchar(100),death_city varchar(100), birth_city_id int(11) REFERENCES cities(city_id),death_city_id int(11)REFERENCES cities(city_id),PRIMARY KEY(id));";
	public static String updatePeopleTable="INSERT into people(id, name,birth_city,death_city) Values (?,?,?,?);";
	//TODO update foreign key
	public static String updatePeopleTableBirthCityId="UPDATE  people LEFT JOIN; cities; ON people.birth_city = cities.city_name; SET people.birth_city_id = cities.city_id;";
	public static String updatePeopleTableDeathCityId="UPDATE  people LEFT JOIN; cities; ON  people.death_city = cities.city_name; SET people.death_city_id = cities.city_id;";

	
	/*personCategories- create,insert*/
	public static String dropPeopleCategoriesTable="Drop table if exists peopleCategories;";
	public static String createPeopleCategoriesTable="CREATE table peopleCategories(person_id INT(11) NOT NULL , category_id  INT(11) NOT NULL ,   constraint foreign KEY (person_id) REFERENCES people(id) ON DELETE RESTRICT ON UPDATE RESTRICT,constraint FOREIGN KEY (category_id) REFERENCES categories(categoryID) ON DELETE RESTRICT ON UPDATE RESTRICT,PRIMARY KEY (person_id, category_id));";
    public static String updatePeopleCategoriesTable="INSERT into peopleCategories(person_id, category_id) Values(?,?);";         
	
	
	/*users table-create,insert*/
	public static String dropUsersTable="Drop table if exists users;";
	public static String createUsersTable= "CREATE table users( user_id INT(11), user_name varchar(100), pass varchar(100), PRIMARY KEY(user_id), UNIQUE(user_name));";
	public static String updateUsersTable="INSERT into users(name,password ) Values(?,?);";
	
	/* group table- create, insert*/
	public static String dropGroupTable="Drop table if exists group;";
	public static String createGroupTable="CREATE table groups( group_id INT(11) auto_increment, group_name varchar(100), number_of_questions int(11) DEFAULT 10, citiesByCountries INT(11), citiesByContinent INT(11), PRIMARY KEY(group_id), "+
		"constraint FOREIGN KEY (citiesByCountries) references countries(country_id) ON DELETE RESTRICT ON UPDATE RESTRICT, "+
			"constraint Foreign key (citiesByContinent) references continents(id) on delete restrict on update restrict);";
	public static String updateGroupTable="INSERT into group(groupID, groupName) Values (?,?);";
    

	
}
